package dao.impl;

import bean.CommodityBean;
import dao.BaseDao;
import dao.CommodityDao;
import entity.Commodity;
import entity.CommodityClass;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

public class CommodityDaoImpl extends BaseDao implements CommodityDao {
    @Override
    public List<Commodity> findAllByName(String name) {
        String sql = "select * from commodity where name like ?";
        List<Commodity> list = new ArrayList<>();
        ResultSet rs = super.executeQuery(sql,"%"+name+"%");
        return getCommodities(list, rs);
    }

    @Override
    public List<CommodityBean> findAllMsg() {
        String sql = "SELECT * FROM commodity com INNER JOIN commodity_class comcla on com.class_id = comcla.id";
        List<CommodityBean> list = new ArrayList<>();
        ResultSet rs = super.executeQuery(sql);
        try {
            while(rs.next()){
                CommodityBean commodityBean = new CommodityBean();
                commodityBean.setId(rs.getInt("id"));
                commodityBean.setName(rs.getString("name"));
                commodityBean.setImgUrl1(rs.getString("img_url1"));
                commodityBean.setImgUrl2(rs.getString("img_url2"));
                commodityBean.setImgUrl3(rs.getString("img_url3"));
                commodityBean.setImgUrl4(rs.getString("img_url4"));
                commodityBean.setImgUrl5(rs.getString("img_url5"));
                commodityBean.setInfo(rs.getString("info"));
                commodityBean.setClassId(rs.getInt("class_id"));
                commodityBean.setClassName(rs.getString("class_name"));
                commodityBean.setPrice(rs.getDouble("price"));
                commodityBean.setNumber(rs.getInt("number"));
                commodityBean.setCount(rs.getInt("count"));
                commodityBean.setShelfTime(rs.getDate("shelf_time"));
                list.add(commodityBean);
            }
        }catch (SQLException e){
            e.printStackTrace();
        }
        return list;
    }

    @Override
    public List<Commodity> findScorePage(String name, int startPage, int limit) {
        String sql = "select * from commodity where name like ? limit ?,?";
        List<Commodity> list = new ArrayList<>();
        ResultSet rs = super.executeQuery(sql,"%"+name+"%",startPage,limit);
        return getCommodities(list, rs);
    }

    private List<Commodity> getCommodities(List<Commodity> list, ResultSet rs) {
        try {
            while(rs.next()){
                Commodity commodity = new Commodity();
                commodity.setId(rs.getInt("id"));
                commodity.setName(rs.getString("name"));
                commodity.setCount(rs.getInt("count"));
                commodity.setClassId(rs.getInt("class_id"));
                commodity.setInfo(rs.getString("info"));
                commodity.setImgUrl1(rs.getString("img_url1"));
                commodity.setImgUrl2(rs.getString("img_url2"));
                commodity.setImgUrl3(rs.getString("img_url3"));
                commodity.setImgUrl4(rs.getString("img_url4"));
                commodity.setImgUrl5(rs.getString("img_url5"));
                commodity.setNumber(rs.getInt("number"));
                commodity.setPrice(rs.getDouble("price"));
                commodity.setShelfTime(rs.getDate("shelf_time"));
                list.add(commodity);
            }
        }catch (SQLException e){
            e.printStackTrace();
        }
        return list;
    }

    @Override
    public Commodity findById(int id) {
        String sql = "select * from commodity where id = ?";
        ResultSet rs = super.executeQuery(sql,id);
        try {
            if(rs.next()){
                Commodity commodity = new Commodity();
                commodity.setId(rs.getInt("id"));
                commodity.setName(rs.getString("name"));
                commodity.setCount(rs.getInt("count"));
                commodity.setClassId(rs.getInt("class_id"));
                commodity.setInfo(rs.getString("info"));
                commodity.setImgUrl1(rs.getString("img_url1"));
                commodity.setImgUrl2(rs.getString("img_url2"));
                commodity.setImgUrl3(rs.getString("img_url3"));
                commodity.setImgUrl4(rs.getString("img_url4"));
                commodity.setImgUrl5(rs.getString("img_url5"));
                commodity.setNumber(rs.getInt("number"));
                commodity.setPrice(rs.getDouble("price"));
                commodity.setShelfTime(rs.getDate("shelf_time"));
                return commodity;
            }
        }catch (SQLException e){
            e.printStackTrace();
        }
        return null;
    }

    @Override
    public List<CommodityBean> findAllByNameAndClass(String name, String id,int startPage,int limit) {
        ResultSet rs = null;
        List<CommodityBean> list = new ArrayList<>();
        System.out.println("gggg:"+name+"-"+id+"-"+startPage+"-"+limit);
        String sql = "SELECT * FROM commodity comdit INNER JOIN commodity_class comclas on comdit.class_id = comclas.id where comdit.name like ? and comclas.id like ? order by comdit.id limit ?,?";
        rs = super.executeQuery(sql,"%"+name+"%","%"+id+"%",startPage,limit);
        try {
            while(rs.next()){
                CommodityBean commodityBean = new CommodityBean();
                commodityBean.setId(rs.getInt("id"));
                commodityBean.setName(rs.getString("name"));
                commodityBean.setImgUrl1(rs.getString("img_url1"));
                commodityBean.setImgUrl2(rs.getString("img_url2"));
                commodityBean.setImgUrl3(rs.getString("img_url3"));
                commodityBean.setImgUrl4(rs.getString("img_url4"));
                commodityBean.setImgUrl5(rs.getString("img_url5"));
                commodityBean.setInfo(rs.getString("info"));
                commodityBean.setClassId(rs.getInt("class_id"));
                commodityBean.setClassName(rs.getString("class_name"));
                commodityBean.setPrice(rs.getDouble("price"));
                commodityBean.setNumber(rs.getInt("number"));
                commodityBean.setCount(rs.getInt("count"));
                commodityBean.setShelfTime(rs.getDate("shelf_time"));
                list.add(commodityBean);
            }
        }catch (SQLException e){
            e.printStackTrace();
        }
        return list;
    }

    @Override
    public int delId(int id) {
        String sql = "DELETE FROM commodity WHERE id = ?";
        return executeUpdate(sql,id);
    }

    @Override
    public int addAll(Commodity commodity) {
        String sql = "INSERT INTO commodity(`name`,img_url5,info,class_id,price,number,count,shelf_time) VALUES(?,?,?,?,?,?,?,?)";
        return executeUpdate(sql,commodity.getName(),commodity.getImgUrl5(),commodity.getInfo(),commodity.getClassId(),commodity.getPrice(),commodity.getNumber(),commodity.getCount(),commodity.getShelfTime());
    }

    @Override
    public int update(Commodity commodity) {
        String sql = "UPDATE commodity SET `name`=?,info=?,class_id=?,price=?,number=?,count=?,shelf_time=? WHERE id=?";
        return executeUpdate(sql,commodity.getName(),commodity.getInfo(),commodity.getClassId(),commodity.getPrice(),
                commodity.getNumber(),commodity.getCount(),commodity.getShelfTime(),commodity.getId());
    }

    @Override
    public List<Commodity> findId(int id) {
        String sql = "SELECT * FROM commodity WHERE id = ?";
        List<Commodity> list = new ArrayList<>();
        ResultSet rs = executeQuery(sql,id);
        try {
            while (rs.next()){
                Commodity commodity = new Commodity();
                commodity.setId(rs.getInt("id"));
                commodity.setName(rs.getString("name"));
                commodity.setCount(rs.getInt("count"));
                commodity.setClassId(rs.getInt("class_id"));
                commodity.setInfo(rs.getString("info"));
                commodity.setImgUrl1(rs.getString("img_url1"));
                commodity.setImgUrl2(rs.getString("img_url2"));
                commodity.setImgUrl3(rs.getString("img_url3"));
                commodity.setImgUrl4(rs.getString("img_url4"));
                commodity.setImgUrl5(rs.getString("img_url5"));
                commodity.setNumber(rs.getInt("number"));
                commodity.setPrice(rs.getDouble("price"));
                commodity.setShelfTime(rs.getDate("shelf_time"));
                list.add(commodity);
            }
            return list;
        }catch (SQLException e){
            e.printStackTrace();
        }
        return null;
    }

    @Override
    public int findCountPage(String name, String id) {
        String sql = "SELECT count(*) FROM commodity comdit INNER JOIN commodity_class comclas on comdit.class_id = comclas.id where comdit.name like ? and comclas.id like ?";
        ResultSet rs = super.executeQuery(sql,"%"+name+"%","%"+id+"%");
        try {
            if(rs.next()){
                return rs.getInt(1);
            }
        }catch (SQLException e){
            e.printStackTrace();
        }
        return -1;
    }
}
